#!/usr/bin/python3
# coding=utf-8
# Author: Sunck
import pymysql
from crawler_baidu.mysql.operate_server import Jump_Server
from logger import LOG


def connect_db(host):
    """
    登录跳板机,获取目标机的mysql服务并且返回服务
    :param host: 服务器登录信息
    :return: 返回目标机的mysql服务
    """
    # 私钥登录服务器
    if "ssh_pkey" in host.keys():
        server = Jump_Server().login_server(
            address=host["address"],
            address_port=host["address_port"],
            username=host["username"],
            remote_address=host["remote_address"],
            remote_address_port=host["remote_address_port"],
            ssh_pkey=host["ssh_pkey"],
            key_password=["key_password"]
        )
    # 密码登录服务器
    elif "password" in host.keys():
        server = Jump_Server().login_server(
            address=host["address"],
            address_port=host["address_port"],
            username=host["username"],
            password=host["password"],
            remote_address=host["remote_address"],
            remote_address_port=host["remote_address_port"],
            local_bind_address=('127.0.0.1', 9001),
        )
    else:
        LOG.warning("登录方法错误，传参密钥登录或密码登录, 抛出异常 - ValueError")
        raise ValueError
    return server


def mysql_connect(db_name):
    """
    获取登录跳板机服务,并且获得目标机的mysql服务,对sql语句执行操作
    :param db_name: 数据库名称
    :return: 不返回
    """
    # 登录数据库
    db = pymysql.connect(
        host='127.0.0.1',  # mysql地址
        port=server.local_bind_port,  # 跳板机绑定的端口号
        user='root',  # mysql账号
        passwd='mysql5@Crm..',  # mysql密码
        db=db_name,  # 数据库名
        charset='utf8mb4'
    )
    return db


def python_sql_mysql(db_name, sql, is_return=False):
    """
    python操作mysql的接口
    :param db_name: 操作库的名称
    :param sql:
    :param is_return:
    :return:
    """
    db = mysql_connect(db_name=db_name)
    with db:
        with db.cursor() as cursor:
            cursor.execute(sql)
            if is_return:  # 判断是否返回,如果为True对齐进行查询操作,如果为False则进行插入操作
                res = cursor.fetchall()
                return res
            else:
                db.commit()


hosts2 = {"address": "36.133.84.86", "address_port": 22,
          "username": "root",
          "remote_address": "192.168.30.22", "remote_address_port": 3306,
          "password": "Shuli@123"}

server = connect_db(host=hosts2)

if __name__ == "__main__":
    # 方案一:通过私钥进行连接
    # hosts1 = {"address": "跳板机IP", "address_port": "跳板机登录端口",
    #           "username": "跳板机登录账号",
    #           "remote_address": "mysql服务器IP", "remote_address_port": "数据库端口号",
    #           "ssh_pkey": "私钥路径", "key_password": "私钥密码"}
    # # 通过密钥登录跳板机
    # connect_db(sql=sql, host=hosts1, db=dbs)
    # 方案二:通过账号密码进行跳板机的连接
    # hosts2 = {"address": "36.133.87.223", "address_port": 22,
    #           "username": "root",
    #           "remote_address": "192.168.30.22", "remote_address_port": 3306,
    #           "password": "Shuli@123"}

    # dbs = {"address": "192.168.30.22", "user": "root", "password": "Root@123",
    #        "db": "test"}
    # server = connect_db(host=hosts2)
    for i in range(10):
        sql = "insert into test_1 (price) VALUES ({})".format(i)
        python_sql_mysql(db_name='test', sql=sql, is_return=False)
    Jump_Server().logout_server(server_pointer=server)
